﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using CLEX.Service.DataContracts;

namespace CLEX.Database.DAL
{
    public class SchemaDAL : ISchemaDAL
    {
        private const string TABLE_CATALOG_COL = "TABLE_CATALOG";
        private const string TABLE_SCHEMA_COL = "TABLE_SCHEMA";
        private const string TABLE_NAME_COL = "TABLE_NAME";
        private const string COLUMN_NAME_COL = "COLUMN_NAME";
        private const string ORDINAL_POSITION_COL = "ORDINAL_POSITION";
        private const string DATA_TYPE_COL = "DATA_TYPE";

        public List<TableData> GetSchema(string connectionString)
        {
            var catalogs = new Dictionary<string, Dictionary<string, Dictionary<string, List<ColumnData>>>>();

            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                conn.Open();
                using (SqlCommand cmd = new SqlCommand("select * from information_schema.columns", conn))
                {
                    cmd.CommandType = CommandType.Text;

                    using (SqlDataReader reader = cmd.ExecuteReader())
                    {
                        int tableCatalogOrd = reader.GetOrdinal(TABLE_CATALOG_COL);
                        int tableSchemaOrd = reader.GetOrdinal(TABLE_SCHEMA_COL);
                        int tableNameOrd = reader.GetOrdinal(TABLE_NAME_COL);
                        int columnNameOrd = reader.GetOrdinal(COLUMN_NAME_COL);
                        int ordinalPositionOrd = reader.GetOrdinal(ORDINAL_POSITION_COL);
                        int dataTypeOrd = reader.GetOrdinal(DATA_TYPE_COL);

                        while (reader.Read())
                        {
                            string tableCatalog = reader.GetString(tableCatalogOrd);
                            string tableSchema = reader.GetString(tableSchemaOrd);
                            string tableName = reader.GetString(tableNameOrd);
                            string columnName = reader.GetString(columnNameOrd);
                            int ordinalPosition = reader.GetInt32(ordinalPositionOrd);
                            string dataType = reader.GetString(dataTypeOrd);

                            Dictionary<string, Dictionary<string, List<ColumnData>>> catalog;
                            if (!catalogs.TryGetValue(tableCatalog, out catalog))
                            {
                                catalog = new Dictionary<string, Dictionary<string, List<ColumnData>>>();
                                catalogs.Add(tableCatalog, catalog);
                            }

                            Dictionary<string, List<ColumnData>> schema;
                            if (!catalog.TryGetValue(tableSchema, out schema))
                            {
                                schema = new Dictionary<string, List<ColumnData>>();
                                catalog.Add(tableSchema, schema);
                            }

                            List<ColumnData> columns;
                            if (!schema.TryGetValue(tableName, out columns))
                            {
                                columns = new List<ColumnData>();
                                schema.Add(tableName, columns);
                            }

                            ColumnData column = new ColumnData(
                                name: columnName,
                                dataType: dataType
                                );
                            while (columns.Count < ordinalPosition)
                            {
                                columns.Add(null);
                            }
                            columns[ordinalPosition - 1] = column;// oridinal position starts from 1.
                        }
                    }
                }
            }

            List<TableData> tables = new List<TableData>();
            foreach (var catalog in catalogs.OrderBy(p => p.Key))
            {
                foreach (var schema in catalog.Value.OrderBy(p => p.Key))
                {
                    foreach (var table in schema.Value.OrderBy(p => p.Key))
                    {
                        tables.Add(new TableData(catalog.Key, schema.Key, table.Key, table.Value.OrderBy(p => p.Name)));
                    }
                }
            }

            return tables;
        }
    }
}
